#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2024/1/29 10:33
# @Author : 闫峰
# @File    : get_sku_goor_num.py
from common.setting import ensure_path_sep
from utils.mysqlUtils.db_pool import Database


product_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
warehouse_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")

class Inventory:

    def __init__(self,sku,wareHouse_id_set):
        """
        废弃
        """
        self.env = 'test_'
        self.sku = sku
        self.wareHouse_id_set = wareHouse_id_set
        is_group_sql = """SELECT is_group FROM test_zhcxkj_center_product.rb_product WHERE sku=%s;"""
        is_group_sql_res = product_db.fetch_all(is_group_sql, (self.sku,))
        self.is_group = is_group_sql_res[0]['is_group']

    def get_local_num(self):
        if self.is_group == 0:
            get_local_good_num = f"""
            SELECT SUM(good_num) good_num FROM {self.env}zhcxkj_center_warehouse.dynamic_inventory WHERE sku=%s
            and warehouse_id in %s;
            """
            local_good_num = warehouse_db.fetch_all(get_local_good_num, (self.sku,self.wareHouse_id_set))
            if not local_good_num[0].get('good_num'):
                return 0
            if local_good_num[0].get('good_num'):
                # print(f"{sku}动态库存：{res1[0].get('good_num')}")
                return local_good_num[0].get('good_num')

        else:
            get_child_sku = "SELECT sku,sku_num FROM test_zhcxkj_center_product.rb_product_group_sku " \
                            "WHERE group_sku=%s;"
            get_child_sku_res = product_db.fetch_all(get_child_sku, (self.sku,))
            if not get_child_sku_res:
                # raise Exception("组合产品库查询不到子sku")
                return 0

            if get_child_sku_res:
                sku_list = []
                for child in get_child_sku_res:
                    filter_good_num = f"""
                        SELECT warehouse_id,FLOOR(good_num/{child.get('sku_num')}) good_num FROM test_zhcxkj_center_warehouse.dynamic_inventory 
                        WHERE sku = %s  
                        and warehouse_id in %s
                        """
                    filter_good_num_res = warehouse_db.fetch_all(filter_good_num, (child.get('sku'),self.wareHouse_id_set))

                    if filter_good_num_res:
                        # min_good_num = min(d['good_num'] for d in filter_good_num_res)
                        good_num = sum(d['good_num'] for d in filter_good_num_res)
                    else:
                        good_num = 0
                    # dongtaikucun = int(good_num / (child.get('sku_num')))
                    sku_list.append((child.get('sku'), good_num))
                min_sku = sorted(sku_list, key=lambda x: x[1], reverse=False)[0]
                finally_good_num = min_sku[1]
                # print(f"{sku}动态库存：{finally_good_num}")
                return finally_good_num



    def get_overseas_num(self):
        if self.is_group == 0:
            get_local_good_num = """
            SELECT SUM(good_num) good_num FROM test_zhcxkj_center_warehouse.rb_ware_house_product WHERE sku=%s
            and warehouse_id in %s;
            """
            local_good_num = warehouse_db.fetch_all(get_local_good_num, (self.sku,self.wareHouse_id_set))
            if not local_good_num:
                return 0
            if local_good_num[0].get('good_num') is None:
                return 0
            return local_good_num[0].get('good_num')
        else:
            get_child_sku = "SELECT sku,sku_num FROM test_zhcxkj_center_product.rb_product_group_sku " \
                            "WHERE group_sku=%s;"
            get_child_sku_res = product_db.fetch_all(get_child_sku, (self.sku,))
            if not get_child_sku_res:
                raise Exception("组合产品库查询不到子sku")
            if get_child_sku_res:
                sku_list = []
                for child in get_child_sku_res:
                    filter_good_num = f"""
                        SELECT wareHouse_id,FLOOR(good_num/{child.get('sku_num')}) good_num FROM test_zhcxkj_center_warehouse.rb_ware_house_product 
                        WHERE sku = %s  
                        and wareHouse_id in %s
                        """
                    filter_good_num_res = warehouse_db.fetch_all(filter_good_num, (child.get('sku'),self.wareHouse_id_set))
                    if filter_good_num_res:
                        good_num = sum(d['good_num'] for d in filter_good_num_res)
                    else:
                        good_num = 0
                    sku_list.append((child.get('sku'), good_num))
                min_sku = sorted(sku_list, key=lambda x: x[1], reverse=False)[0]
                finally_good_num = min_sku[1]
                # print(f"{sku}动态库存：{finally_good_num}")
                return finally_good_num



    def get_total(self):
        local_num = self.get_local_num()
        overseas_num = self.get_overseas_num()
        # print(f"local_num={local_num}")
        # print(f"overseas_num={overseas_num}")
        return local_num+overseas_num


if __name__ == '__main__':
    a = ['11308167', '16953990', '4473684', '10280698', '16683003', '5618124', '7031649', '12142285', '11943420',
         '12788392', '16131377', '15905730', '11405352', '16020223', '16108583', '16082219', '11913255', '13250421',
         '9117927', '9512446', '14642528', '1583506', '13543377', '17362714', '14449506', '5660770', '5851441',
         '6224417', '16725236', '7493609', '3898554', '8732760', '15834024', '16152875', '11922459', '4971486',
         '9794856', '9502286', '8162135', '6881621', '10915881', '16795367', '15885639', '16233861', '9271133',
         '8272099', '5683908', '13617428', '11669304', '8855378', '10442854', '9422153', '10625742', '13343718',
         '7214631', '15543399', '17156855', '15696837', '16444871', '2642622', '12239006', '11448803', '11173455',
         '15863768', '14990869', '3154037', '8024539', '4790382', '16849754', '10619046', '15391788', '9255610',
         '17194988', '7879069', '14629135', '11915650', '6314294', '5847782', '13974655', '14991596', '12421541',
         '12133716', '10411684', '14716813', '3408311', '6134404', '2232764', '16724789', '2006501', '5659323',
         '11200827', '8330186', '14091467', '1934444', '17106851', '14975553', '1752858', '5352778', '5688587',
         '10233678', '17111273', '17428716', '9485237', '14974226', '9273561', '11937220', '17154387', '16790553',
         '14443054', '1639940', '10564508', '6132849', '17176448', '7370610', '16136267', '14172825', '8826111',
         '13993424', '13705333', '12177301', '9825214', '11202829', '14533440', '3311008', '13987871', '11411023',
         '13124944', '5536251', '16036792', '16606564', '6393009', '5844585', '16136270', '8251488', '14456016',
         '6437737', '11187001', '15399575', '8659807', '8914823', '14400150', '14337106', '5685129', '11250007',
         '5820177', '3224969', '17280717', '14456079', '11705893', '11771657', '14342876', '12082465', '15658671',
         '14833783', '10421461', '14142623', '16273075', '12015880', '13284319', '7396525', '5859178', '8902576',
         '8645588', '12176613', '5129509', '13754309', '10158491', '4767786', '8422974', '15919760', '14818954',
         '7786341', '5995450', '16299382', '8805704', '11419111', '11259795', '14092590', '8599599', '6314290',
         '10768228', '6145867', '16028106', '14766642', '7379217', '8148544', '8512852', '13030454', '12466754',
         '6994973', '6385019', '3793926', '6219959', '15992593', '10261792', '13651798', '15987406', '11608830',
         '13730958', '7921086', '11481587', '14293982', '5132977', '3704296', '9132603', '4005900', '7216900',
         '14510035', '13678922', '14337965', '7027413', '17105559', '9249070', '16081811', '4186402', '11310638',
         '8631327', '15882795', '12642171', '13999897', '12176033', '15037254', '7144781', '11111155', '9355097',
         '9823604', '17146235', '5920995', '17279820', '16313791', '5453395', '14856078', '8029018', '4251960',
         '16576309', '15964900', '16040531', '9024619', '2409024', '14529287', '8710598', '2147821', '5403549',
         '9332165', '10075183', '8725056', '11147376', '9600876', '15588055', '1949373', '14281070', '7462130',
         '14082949', '15619415', '15862038', '8901858', '5506122', '1956818', '14334236', '6319698', '7469656',
         '9429459', '14493428', '4179119', '1863447', '14663799', '5699510', '14811870', '3049646', '5724719',
         '12646793', '11533032', '11385703', '15004395', '3206979', '4391821', '5700052', '15663400', '6108845',
         '14629478', '8315748', '8705666', '13900765', '15237212', '13558262', '4048013', '14423979', '15877701',
         '3825233', '11066790', '10267910', '7171877', '2496580', '13575029', '14759728', '11655149', '15544338',
         '11938076', '4608188', '14334204', '8748912', '8935451', '2430691', '11165446', '2989899', '2296426',
         '12353970', '8531778', '16862947', '13599521', '14833706', '16780130', '1628160', '14164257', '14455534',
         '9395121', '13146711', '10288363', '15683965', '13774696', '12362557', '3997692', '10921392', '12025087',
         '14490346', '8149041', '12360543', '9458263', '17319062', '5905293', '14615230', '14371892', '10577821',
         '16864513', '5766990', '4641554', '14120219', '8570098', '14759680', '17063778', '8813896', '16685535',
         '8379355', '8198352', '11862575', '10084773', '12370711', '10967828', '6919103', '15588063', '8608544',
         '16840336', '14333742', '15810050', '15861949', '5309294', '9861017', '16254890', '13436467', '15216864',
         '11365534', '12650071', '3705097', '4939452', '13056972', '11185130', '6990428', '4221589', '5736261',
         '15514555', '9355040', '14422540', '17025907', '16079097', '16026709', '3154948', '5386915', '9660932',
         '11817321', '7117335', '14722603', '9528508', '16941116', '10281069', '8354853', '15369399', '12303055',
         '14174396', '5227874', '13198281', '5970460', '8746296', '14418410', '2477074', '8604881', '16503775',
         '17312562', '8398561', '2341405', '17032050', '6314617', '2433141', '6749114', '2715187', '15026110',
         '5303370', '13616027', '6314293', '10643401', '14485173', '16115403', '7871858', '17442347', '16040561',
         '15955828', '3488663', '8439341', '5866595', '8589625', '17344849', '7755073', '12406488', '13443232',
         '9563608', '14485576', '3065938', '14891549', '14328063', '10223219', '17502204', '10732333', '16710486',
         '4995786', '15549083', '16397557', '10786775', '11788964', '17480261', '12613693', '7690450', '3606480',
         '11294141', '9360903', '10618812', '15732334', '13042472', '10281029', '16026782', '10818376', '3329238',
         '10138466', '8409618', '16552253', '15374622', '2480308', '5964846', '6437832', '13177495', '16297393',
         '10520826', '13411955', '17393733', '7372213', '13603775', '16929304', '9735152', '5888883', '16637049',
         '5724454', '11658986', '10768557', '11514119', '10422501', '14056954', '12023739', '16262946', '14996539',
         '14143816', '11196742', '5261256', '16190962', '16126250', '17175427', '3489348', '13790060', '10017106',
         '11972495', '8261246', '11247138', '14478311', '9150610', '4298325', '2847799', '12023538', '7991571',
         '5643924', '14580278', '6234088', '6466490', '5326830', '14327657', '4758425', '3195388', '5885807',
         '15863750', '15850469', '7650744', '8463932', '5764589', '7677793', '13350031', '13328052', '14037463',
         '16244056', '13631289', '5008136', '5105947', '11674180', '10486587', '8208927', '7206130', '4090352',
         '13993893', '10709840', '5694695', '13942313', '6387992', '11039336', '9518592', '11647178', '8335063',
         '6867825', '16636136', '9528204', '9443681', '7982825', '4025781', '11311092', '11759350', '5175232',
         '10190984', '11617643', '13257574', '9823765', '8940075', '14944101', '14037210', '8376024', '13058125',
         '13743946', '15892970', '13927066', '8170514', '13443141', '3834465', '6112388', '5829400', '5422135',
         '14960320', '17502566', '3165135', '13334228', '14977556', '5381432', '17317282', '13891876', '14609434',
         '6470911', '16374410', '10956402', '10405939', '8872063', '9702657', '12625289', '10325557', '9817716',
         '7898508', '6941481', '9548833', '14399408', '3949086', '15997691', '17412267', '6889106', '5209782',
         '16448839', '5306233', '9425493', '11504173', '14497056', '13179169', '11994792', '14375038', '14105099',
         '3154871', '13390998', '9697033', '14636733', '8438261', '3521209', '14647201', '14465254', '15579977',
         '15602674', '16808942', '6741257', '15638222', '11903820', '12335568', '5824025', '17390236', '16267447',
         '13364710', '14662763', '13577421', '15953720', '14715343', '2197592', '12531963', '10626241', '8472987',
         '13376795', '11323719', '10688670', '7519112', '17247046', '11490872', '14506559', '14425158', '3564806',
         '16902511', '17168143', '2478805', '11501242', '11288090', '15915124', '14201946', '10622326', '11659510',
         '13234340', '13153110', '11439352', '13479158', '14752685', '12214092', '5892160', '4803960', '13911175',
         '8065003', '14784713', '11924657', '8951405', '17523866', '6174930', '5900273', '11914355', '10641036',
         '10249854', '14752678', '14422534', '10592129', '11044954', '9234583', '15552351', '16693911', '8727615',
         '8263291', '17146642', '6020630', '3770132', '10986641', '14837044', '8003757', '13105008', '15745484',
         '7101566', '4828645', '13094082', '15492734', '13518374', '6839081', '13502175', '3978008', '11116217',
         '8200764', '13441111', '5221318', '8181921', '7890620', '12641728', '4733689', '6029035', '15820268',
         '5695537', '4725304', '13478996', '12454819', '15733800', '7607863', '7370076', '16554938', '8379824',
         '9709717', '14859906', '14510027', '5291473', '17168339', '9553521', '10431617', '14276908', '8812314',
         '2242989', '8744095', '4356221', '13292531', '1611371', '14139505', '14475822', '14965858', '8890945',
         '5966078', '11489394', '2433395', '14093923', '11967310', '12228595', '8331215', '6060311', '9270046',
         '17140358', '3127546', '11334594', '3841612', '8309839', '3593002', '15032566', '16261942', '14322748',
         '16132209', '10815339', '6587301', '12101751', '6485431', '5844457', '8665080', '11409672', '13619863',
         '10344279', '10614503', '14596455', '5392466', '11329873', '8563598', '11441491', '14583317', '13146658',
         '10474260', '6707205', '7161711', '14664548', '7306271', '4574504', '9615511', '17127662', '3461410',
         '16262647']

    # a =["9458263","5900273","6466490","6587301","6741257","7144781","7214631","7306271","7372213","7379217","7462130","7469656","7493609","9553521","10405939","10411684","10421461","10431617","2296426","11501242","2715187","13094082","9117927","8354853","2242989","13730958","11411023","17442347","9485237","14596455","10577821","4356221","10520826","10614503","14337965","10688670","10486587","17247046","16693911","17312562","10280698","13284319","16020223","13575029","2433395","13999897","14944101","14172825","11405352","13234340"]
    # b = []
    sku_good_num_dict = {}
    for sku in a:
        sku_good_num_dict[sku] = int(Inventory(sku, {5312, 333}).get_local_num())
        print(f"{sku}:{Inventory(sku, {5312, 333}).get_local_num()}")
    print(sku_good_num_dict)